const cors = require('cors');
const moment = require('moment');
const database  = require('./database')
const port = 3000;

const user = require('./user');
const express = require('express');
const app = express();
app.use(express.json()); 
app.use(express.urlencoded({ extended: true }));

app.use(cors({
    origin: 'http://localhost:8080'
}));
app.use('/api', user);

app.get('/', (req, res) => {
    res.send('Welcome to the warehouse management system!');
  });

// 获取所有入库记录
app.get('/api/warehouse_records', async (req, res) => {
    const { filter, sortBy } = req.query;  // 获取前端发送的查询参数
  
    try {
      let query = 'SELECT * FROM warehouse_records';
  
      // 根据前端发送的查询参数构建查询语句
      if (filter) {
        query += ` WHERE column_name = '${filter}'`;
      }
  
      if (sortBy) {
        query += ` ORDER BY ${sortBy}`;
      }
  
      const [rows, fields] = await database.query(query);
      rows.forEach(record => {
        record.entry_date = moment(record.entry_date).format('YYYY-MM-DD');
        record.departure_date = moment(record.departure_date).format('YYYY-MM-DD');
      });
      res.json(rows);
    } catch (err) {
      console.error('Error getting warehouse records: ', err);
      res.status(500).json({ error: 'Error getting warehouse records' });
    }
  });

// 添加新的入库记录
app.post('/api/warehouse_records', async (req, res) => {
    const newRecord = {
      entry_date: req.body.entry_date || null,
      plate_number: req.body.plate_number || null,
      entry_bags: req.body.entry_bags || null,
      entry_tons: req.body.entry_tons || null,
      departure_date: req.body.departure_date || null,
      order_number: req.body.order_number || null,
      departure_bags: req.body.departure_bags || null,
      departure_tons: req.body.departure_tons || null,
      bill_number: req.body.bill_number || null,
      business_number: req.body.business_number || null,
      remaining_tons: req.body.remaining_tons || null,
      storage_fee: req.body.storage_fee || null
    };
    try {
        //执行插入操作
        const query = `
          INSERT INTO warehouse_records 
          (entry_date, plate_number, entry_bags, entry_tons, departure_date, order_number, departure_bags, departure_tons, bill_number, business_number, remaining_tons, storage_fee)
          VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`;
        const [result] = await database.query(query, Object.values(newRecord));
        newRecord.id = result.insertId;  // 获取插入记录的ID
        // 调用存储过程来更新剩余吨量
        await updateRemainingTons();

        res.status(201).json(newRecord);
      } catch (err) {
        console.error('Error inserting new warehouse record:', err.message);  // 打印错误消息
        console.error('Error stack trace:', err.stack);  // 打印错误堆栈
        res.status(500).json({ error: 'Error inserting new warehouse record' });
      }
    });
    // 定义更新剩余吨量的存储过程调用函数
async function updateRemainingTons() {
    try {
      const connection = await pool.getConnection();
      try {
        // 调用存储过程
        await connection.query('CALL update_remaining_tons()');
      } finally {
        connection.release();  // 释放连接
      }
    } catch (err) {
      console.error('Error updating remaining tons:', err.message);  // 打印错误消息
      console.error('Error stack trace:', err.stack);  // 打印错误堆栈
      throw err;
    }
  }

  

// 删除指定ID的入库记录
app.delete('/api/warehouse_records/:id', async (req, res) => {
    const { id } = req.params;
  
    try {
      const query = 'DELETE FROM warehouse_records WHERE id = ?';
      await database.query(query, [id]);
      res.status(204).end();
    } catch (err) {
      console.error('Error deleting warehouse record:', err);
      res.status(500).json({ error: 'Error deleting warehouse record' });
    }
  });

app.listen(port, () => {
  console.log(`Server is running at http://localhost:${port}`);
});
